﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace MechanicCalculator.Utility
{
    class ExcelManagement
    {
        #region Constants
        public static readonly String ID = "ID";
        public static readonly String MEASURE_SYSTEM_LENGTH   = "MeasureSystem_Length";
        public static readonly String MEASURE_SYSTEM_PRESSURE = "MeasureSystem_Pressure";
        public static readonly String FLANGE_CLASS = "FlangeClass";
        public static readonly String NOMINAL_FLANGE_SIZE = "NominalFlangeSize";
        public static readonly String BOLT_GRADE = "BoltGrade";
        public static readonly String TARGET_PERCENT_OF_YIELD = "TargetPercentOfYield";
        public static readonly String NUMBER_OF_TOOLS = "NumberOfTools";
        public static readonly String WRENCH_TYPE = "WrenchType";
        public static readonly String LOA_DISC = "LoaDisc";
        public static readonly String LUBRICANT = "Lubricant";

        public static readonly string[] COLUMN_NAMES = {"MeasureSystem_Length", "MeasureSystem_Pressure", "FlangeClass", "NominalFlangeSize",
                "BoltGrade", "TargetPercentOfYield", "NumberOfTools", "WrenchType", "LoaDisc", "Lubricant"};
        #endregion

        #region Variables
        private String _strExcelFileName;
        #endregion

        #region Constructor
        public ExcelManagement()
        {
        }
        public ExcelManagement(String strExcelFileName)
        {
            //_strExcelFileName = String.Format("{0}\\{1}", Application.StartupPath, strExcelFileName);
            _strExcelFileName = strExcelFileName;
        }
        #endregion

        #region Properties
        public void setStrExcelFileName(String strExcelFileName)
        {
            _strExcelFileName = strExcelFileName;
        }

        private string ExcelConnection()
        {
            return
                @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                @"Data Source=" + _strExcelFileName + ";" +
                @"Extended Properties='Excel 8.0;HDR=No'";
        }
        #endregion

        #region Basic Functions
        public DataTable GetDataTable(string sql)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            OleDbConnection conn = new OleDbConnection(ExcelConnection());
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
            adpt.Fill(ds);
            dt = ds.Tables[0].Copy();
            conn.Close();
            return dt;
        }

        public int ExecuteNonQuery(string sql)
        {
            // reference http://dotnetbeyond.blogspot.com/2011/09/readwrite-data-in-excel-using-oledb-c.html
            OleDbConnection cnn = new OleDbConnection(ExcelConnection());
            cnn.Open();
            OleDbCommand mycommand = new OleDbCommand(sql, cnn);
            int rowsUpdated = mycommand.ExecuteNonQuery();
            cnn.Close();
            return rowsUpdated;
        }
        #endregion

        #region Utility Functions
        public bool Insert(String sheetName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", sheetName, columns, values));
            }
            catch (Exception fail)
            {
                MessageBox.Show(fail.Message);
                returnCode = false;
            }
            return returnCode;
        }

        public bool Update(String sheetName, Dictionary<String, String> data, String where)
        {
            String vals = "";
            Boolean returnCode = true;
            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }
            try
            {
                this.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", sheetName, vals, where));
            }
            catch
            {
                returnCode = false;
            }
            return returnCode;
        }

        // Reference http://csharp.net-informations.com/excel/csharp-create-excel.htm
        public void CreateExcelFile()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Cells[1, 1] = ID;
            for (int i = 0; i < COLUMN_NAMES.Length; i++)
                xlWorkSheet.Cells[1, i + 2] = COLUMN_NAMES[i];
            xlWorkBook.SaveAs(_strExcelFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }


        // Support Export To Excel From Database
        public bool InsertFromDataTable(DataTable dataTable)
        {
            Boolean returnCode = true;

            foreach (DataRow dr in dataTable.Rows)
            {
                Dictionary<String, String> data = new Dictionary<string, string>();
                int iCount = dr.ItemArray.Length;
                for (int i = 0; i < iCount; i++)
                {
                    data.Add(COLUMN_NAMES[i], dr[i].ToString());
                }
                returnCode = Insert("Sheet1", data);
            }

            return returnCode;
        }

        #endregion
    }
}

